﻿using DBUtils;
using Dos.Common;
using Dos.ORM;
using LibUtils;
using ModelsUtils;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Swashbuckle.Swagger;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Net.NetworkInformation;
using System.Reflection;
using System.Runtime.Remoting.Contexts;
using System.Security.Cryptography;
using System.Security.Principal;
using System.Web.Http;
using System.Web.Services.Description;
using System.Web.UI.WebControls;
using System.Xml.Linq;
using WebApi.Models;
using WebApi.Models.JieShun;
using static System.Net.Mime.MediaTypeNames;
using static WebApi.WebApiApplication;

namespace WebApi.Controllers
{

    public class CommonController : BaseController
    {
        #region 获取待办列表数据

        /// <summary>
        /// 获取待办列表数据----停用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet]
        private Result<List<M_NeedToBe>> GetNeedToBeLists()
        {
            var list = new List<M_NeedToBe>();

            #region 采购申请
            var cgsq = GetOrders(OrderTypeEnum.采购申请);
            if (cgsq.Count > 0)
            {
                list.AddRange(cgsq);
            }
            #endregion

            #region 采购订单
            var cgdd = GetOrders(OrderTypeEnum.采购订单);
            if (cgdd.Count > 0)
            {
                list.AddRange(cgdd);
            }
            #endregion

            #region 外购入库
            var wgrk = GetOrders(OrderTypeEnum.外购入库);
            if (wgrk.Count > 0)
            {
                list.AddRange(wgrk);
            }
            #endregion

            #region 生产领料
            var scll = GetOrders(OrderTypeEnum.生产领料);
            if (scll.Count > 0)
            {
                list.AddRange(scll);
            }
            #endregion

            return new Result<List<M_NeedToBe>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"

            };
        }

        private List<M_NeedToBe> GetOrders(OrderTypeEnum orderTypeEnum)
        {

            var orderType = orderTypeEnum.ToString();
            var tabName = string.Empty;
            var classTypeid = string.Empty;
            var tranType = string.Empty;
            switch (orderTypeEnum)
            {
                case OrderTypeEnum.采购申请:
                    tabName = "AIS20151130222535.dbo.PORequest";
                    classTypeid = "1070";
                    tranType = "";
                    break;
                case OrderTypeEnum.采购订单:
                    tabName = "AIS20151130222535.dbo.POOrder";
                    classTypeid = "1071";
                    tranType = "";
                    break;
                case OrderTypeEnum.外购入库:
                    tabName = "AIS20151130222535.dbo.ICStockBill";
                    classTypeid = "1001";
                    tranType = " AND FTranType =1 ";
                    break;
                case OrderTypeEnum.生产领料:
                    tabName = "AIS20151130222535.dbo.ICStockBill";
                    classTypeid = "1024";
                    tranType = " AND FTranType =24 ";
                    break;
            }
            var list = new List<M_NeedToBe>();
            // var sql = "SELECT * FROM  "+ tabName + "  WHERE  FStatus<>1 AND   FMultiCheckStatus  NOT IN (2,16,64)  " + tranType + "  ORDER BY FDate DESC";
            var sql = "SELECT * FROM  " + tabName + "  WHERE  FStatus=0  AND   FMultiCheckStatus  NOT IN (16,64)  " + tranType + "  ORDER BY FDate DESC";
            var orders = DbSession.Default.FromSql(sql).ToList<PORequest>();
            foreach (var item in orders)
            {
                //下一级待审核的节点信息
                var sql1 = "SELECT FNextLevelTagIndex,FBillID,FTemplateID FROM AIS20151130222535.dbo.ICClassMCStatus" + classTypeid + "  WHERE FBillID = " + item.FInterID + " AND FID IN(SELECT MAX(FID) FROM  AIS20151130222535.dbo.ICClassMCStatus" + classTypeid + "   WHERE FBillID = " + item.FInterID + ")";
                var ICClassMC = DbSession.Default.FromSql(sql1).ToList<ICClassMCStatus>().FirstOrDefault();
                if (ICClassMC.IsNullOrEmpty())
                {
                    continue;
                }
                //上一级审核的人信息
                var sql2 = "SELECT FCheckName, FCheckDate FROM AIS20151130222535.dbo.ICClassMCRecord" + classTypeid + "  WHERE FBillID = " + item.FInterID + "  AND FID = (SELECT MAX(FID) FROM AIS20151130222535.dbo.ICClassMCRecord" + classTypeid + "  WHERE FBillID = " + item.FInterID + " )";
                var ICClassMCR = DbSession.Default.FromSql(sql2).ToList<ICClassMCRecord>().FirstOrDefault();
                if (ICClassMCR.IsNullOrEmpty())
                {
                    continue;
                }
                //下一级待审核节点哪些人可以审核
                var sql3 = "SELECT FCheckRight,FName_CHS FROM AIS20151130222535.dbo.ICClassMCTableInfo WHERE FTemplateID=" + ICClassMC.FTemplateID + " AND FTagIndex=" + ICClassMC.FNextLevelTagIndex;
                var ICClassMCT = DbSession.Default.FromSql(sql3).ToList<ICClassMCTableInfo>().FirstOrDefault();
                if (ICClassMCT.IsNullOrEmpty())
                {
                    continue;
                }
                var m = new M_NeedToBe()
                {
                    OrderNo = item.FBillNo,
                    OrderType = orderType,
                    OrderTime = item.FDate.HasValue ? (Convert.ToDateTime(item.FDate)).ToString("yyyy-MM-dd") : "",
                    ApprovalName = ICClassMCR.FCheckName,
                    ApprovalTime = ICClassMCR.FCheckDate.ToString("yyyy-MM-dd")
                };
                if (ICClassMCT.FCheckRight.IsNullOrEmpty())
                {
                    m.NextApprovalName = ICClassMCT.FName_CHS;
                }
                else
                {
                    List<string> FCheckRights = ICClassMCT.FCheckRight.Split(',').ToList();
                    FCheckRights.Remove(AppSetting.adminId.ToString());
                    foreach (var e in FCheckRights)
                    {
                        bool bl = int.TryParse(e, out int id);
                        if (bl)
                        {
                            var sql4 = "SELECT FUserID,FName FROM AIS20151130222535.dbo.t_User WHERE FUserID=" + id;
                            var user = DbSession.Default.FromSql(sql4).ToList<t_User>().FirstOrDefault();
                            m.NextApprovalName += user.FName + ";";
                        }
                        else
                        {
                            var fieldname = e.Split('_')[1];
                            var sql5 = "SELECT FInterID AS FUserID," + fieldname + " AS FName  FROM  " + tabName + "  WHERE FInterID=" + item.FInterID;
                            var order = DbSession.Default.FromSql(sql5).ToList<t_User>().FirstOrDefault();
                            if (order.IsNullOrEmpty())
                            {
                                m.NextApprovalName = ICClassMCT.FName_CHS;
                            }
                            else
                            {
                                var depid = int.Parse(order.FName.ToString());
                                var sql6 = "SELECT FItemID AS FUserID,FName FROM AIS20151130222535.dbo.t_Department WHERE FItemID=" + depid;
                                var dep = DbSession.Default.FromSql(sql6).ToList<t_User>().FirstOrDefault();
                                m.NextApprovalName += dep.FName + ";";
                            }
                        }
                    }
                }
                list.Add(m);
            }
            return list;
        }

        #endregion

        #region 获取油罐数据

        /// <summary>
        /// 获取油罐分类数据---启用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<string>> GetTankViewSortLists()
        {
            var list = GetTankViews(false);
            var sortnames = list.Select(p => p.fghmc.Substring(2, 3)).DistinctBy(p => p).OrderBy(p => p).ToList();
            return new Result<List<string>>
            {
                code = (int)RespCodeEnum.Success,
                data = sortnames,
                total = sortnames.Count,
                msg = "成功"

            };
        }
        /// <summary>
        /// 获取油罐分类详细数据---启用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<TankView>> GetTankViewSortDetails(string sort)
        {
            if (sort.IsNullOrEmpty())
            {
                return new Result<List<TankView>>
                {
                    code = (int)RespCodeEnum.Failed,
                    msg = "必填字段信息未填写！"
                };
            }
            var list = GetTankViews(false);
            var pairs = list.Where(p => p.fghmc.Contains(sort)).OrderBy(p => p.fghmc).ToList();
            return new Result<List<TankView>>
            {
                code = (int)RespCodeEnum.Success,
                data = pairs,
                total = pairs.Count,
                msg = "成功"
            };
            //Dictionary<string, List<TankView>> pairs = new Dictionary<string, List<TankView>>();
            //var list = GetTankViews(false);
            //var sortnames = list.Select(p => p.fghmc.Substring(0, 5)).DistinctBy(p => p).OrderBy(p => p).ToList();
            //foreach (var item in sortnames)
            //{
            //    var temp = list.Where(p => p.fghmc.Contains(item)).OrderBy(p => p.fghmc).ToList();
            //    pairs.Add(item, temp);
            //}
            //return new Result<Dictionary<string, List<TankView>>>
            //{
            //    code = (int)RespCodeEnum.Success,
            //    data = pairs,
            //    total = pairs.Count,
            //    msg = "成功"

            //};
        }
        /// <summary>
        /// 获取油罐数据---启用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<TankView>> GetTankViewLists(TankView tankView)
        {
            var list = GetTankViews();
            if (!tankView.IsNullOrEmpty())
            {
                if (!tankView.fghmc.IsNullOrEmpty())
                {
                    list = list.Where(p => p.fghmc.Contains(tankView.fghmc)).ToList();
                    //sql += $@" AND fghmc='{tankView.fghmc}'";
                }
                if (!tankView.fjshs.IsNullOrEmpty())
                {
                    if (tankView.fjshs != 0)
                    {
                        list = list.Where(p => p.fjshs == tankView.fjshs).ToList();
                    }
                    // sql += $@" AND fjshs='{tankView.fjshs}'";
                }
                if (!tankView.fyoug.IsNullOrEmpty())
                {
                    if (tankView.fyoug != 0)
                    {
                        list = list.Where(p => p.fyoug == tankView.fyoug).ToList();
                    }
                    // sql += $@" AND fyoug='{tankView.fyoug}'";
                }
                if (!tankView.fyouw.IsNullOrEmpty())
                {
                    if (tankView.fyouw != 0)
                    {
                        list = list.Where(p => p.fyouw == tankView.fyouw).ToList();
                    }
                    //sql += $@" AND fyouw='{tankView.fyouw}'";
                }
                if (!tankView.fypmc.IsNullOrEmpty())
                {
                    list = list.Where(p => p.fypmc.Contains(tankView.fypmc)).ToList();
                    // sql += $@" AND fypmc='{tankView.fypmc}'";
                }
                if (!tankView.fzrkc.IsNullOrEmpty())
                {
                    if (tankView.fzrkc != 0)
                    {
                        list = list.Where(p => p.fzrkc == tankView.fzrkc).ToList();
                    }

                    //sql += $@" AND fzrkc='{tankView.fzrkc}'";
                }
            }
            return new Result<List<TankView>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"

            };
        }
        /// <summary>
        /// 获取油罐Chart数据---启用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<TankChartDate>> GetTankViewChart(TankViewChart tankViewChart)
        {
            if (ModelState.IsValid)
            {
                if (tankViewChart.type == 2)
                {
                    var list = GetTankViews(false);
                    var pairs = list.Where(p => p.fghmc.Contains(tankViewChart.fypmc)).OrderBy(p => p.fghmc).ToList();
                    var listChart = pairs.Select(p => new TankChartDate
                    {
                        DcsData = int.Parse(decimal.Round(p.fjshs, 0, MidpointRounding.AwayFromZero).ToString()),
                        DcsName = p.fghmc
                    }).OrderBy(p => p.DcsData).ToList();
                    return new Result<List<TankChartDate>>
                    {
                        code = (int)RespCodeEnum.Success,
                        data = listChart,
                        total = listChart.Count,
                        msg = ""
                    };
                }
                else
                {
                    var list = GetTankViews();
                    var tempChart = list.Where(p => p.fypmc == tankViewChart.fypmc).ToList();
                    // var sumnum = tempChart.Sum(p => p.fjshs);
                    var listChart = tempChart.Select(p => new TankChartDate
                    {
                        DcsData = int.Parse(decimal.Round(p.fjshs, 0, MidpointRounding.AwayFromZero).ToString()),//p.fjshs,
                        DcsName = p.fghmc
                    }).OrderBy(p => p.DcsData).ToList();
                    // (sumnum.ToString("n")).Substring(0, (sumnum.ToString("n")).Length - 3);
                    //return new Result<List<TankChartDate>>
                    //{
                    //    code = (int)RespCodeEnum.Success,
                    //    data = listChart,
                    //    total = listChart.Count,
                    //    msg = "油品名称：" + tankViewChart.fypmc + "；今日总库存：" + (sumnum.ToString("n")).Substring(0, (sumnum.ToString("n")).Length - 3) + "；"
                    //};
                    //var sumList = list.Select(p => p.fypmc).DistinctBy(p => p).ToList();
                    ////   var msg = string.Empty;
                    //var tankDates = new List<TankDate>();
                    //foreach (var item in sumList)
                    //{
                    //    //DBMain.LogsDebug(item);
                    //    //var temp = list.Where(p => p.fypmc == item).ToList().Sum(p => p.fjshs);
                    //    var temp = list.Where(p => p.fypmc == item).ToList();
                    //    var fjshst = temp.Sum(p => p.fjshs);
                    //    var fjshs = (fjshst.ToString("n")).Substring(0, (fjshst.ToString("n")).Length - 3);
                    //    var fzrkct = temp.Sum(p => p.fzrkc);
                    //    var fzrkc = (fzrkct.ToString("n")).Substring(0, (fzrkct.ToString("n")).Length - 3);
                    //    //var kc = (temp.ToString("n")).Substring(0, (temp.ToString("n")).Length - 3);
                    //    tankDates.Add(new TankDate()
                    //    {
                    //        fypmc = item,
                    //        fjshs = fjshs,
                    //        fzrkc = fzrkc
                    //    });
                    //    // msg += item + "  今日总库存  " + kc + "；";
                    //}
                    return new Result<List<TankChartDate>>
                    {
                        code = (int)RespCodeEnum.Success,
                        data = listChart,
                        total = listChart.Count,
                        //  msg = msg
                    };
                }
            }
            else
            {
                return new Result<List<TankChartDate>>
                {
                    code = (int)RespCodeEnum.Failed,
                    msg = "必填字段信息未填写！"
                };
            }
        }

        /// <summary>
        /// 获取油罐库存数据---启用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<TankDate>> GetTankDate(TankViewChart tankViewChart)
        {
            if (ModelState.IsValid)
            {
                if (tankViewChart.type == 2)
                {
                    //var list = GetTankViews(false);
                    //var pairs = list.Where(p => p.fghmc.Contains(tankViewChart.fypmc)).OrderBy(p => p.fghmc).ToList();
                    //var listChart = pairs.Select(p => new TankChartDate
                    //{
                    //    DcsData = int.Parse(decimal.Round(p.fjshs, 0, MidpointRounding.AwayFromZero).ToString()),
                    //    DcsName = p.fghmc
                    //}).OrderBy(p => p.DcsData).ToList();
                    //return new Result<List<TankChartDate>>
                    //{
                    //    code = (int)RespCodeEnum.Success,
                    //    data = listChart,
                    //    total = listChart.Count,
                    //    msg = ""
                    //};
                    return new Result<List<TankDate>>
                    {
                        code = (int)RespCodeEnum.Success,
                    };
                }
                else
                {
                    var list = GetTankViews();
                    var tempChart = list.Where(p => p.fypmc == tankViewChart.fypmc).ToList();
                    // var sumnum = tempChart.Sum(p => p.fjshs);
                    var listChart = tempChart.Select(p => new TankChartDate
                    {
                        DcsData = int.Parse(decimal.Round(p.fjshs, 0, MidpointRounding.AwayFromZero).ToString()),//p.fjshs,
                        DcsName = p.fghmc
                    }).OrderBy(p => p.DcsData).ToList();
                    // (sumnum.ToString("n")).Substring(0, (sumnum.ToString("n")).Length - 3);
                    //return new Result<List<TankChartDate>>
                    //{
                    //    code = (int)RespCodeEnum.Success,
                    //    data = listChart,
                    //    total = listChart.Count,
                    //    msg = "油品名称：" + tankViewChart.fypmc + "；今日总库存：" + (sumnum.ToString("n")).Substring(0, (sumnum.ToString("n")).Length - 3) + "；"
                    //};
                    var sumList = list.Select(p => p.fypmc).DistinctBy(p => p).ToList();
                    //   var msg = string.Empty;
                    var tankDates = new List<TankDate>();
                    foreach (var item in sumList)
                    {
                        //DBMain.LogsDebug(item);
                        //var temp = list.Where(p => p.fypmc == item).ToList().Sum(p => p.fjshs);
                        var temp = list.Where(p => p.fypmc == item).ToList();
                        var fjshst = temp.Sum(p => p.fjshs);
                        var fjshs = (fjshst.ToString("n")).Substring(0, (fjshst.ToString("n")).Length - 3);
                        var fzrkct = temp.Sum(p => p.fzrkc);
                        var fzrkc = (fzrkct.ToString("n")).Substring(0, (fzrkct.ToString("n")).Length - 3);
                        //var kc = (temp.ToString("n")).Substring(0, (temp.ToString("n")).Length - 3);
                        tankDates.Add(new TankDate()
                        {
                            fypmc = item,
                            fjshs = fjshs,
                            fzrkc = fzrkc
                        });
                        // msg += item + "  今日总库存  " + kc + "；";
                    }
                    return new Result<List<TankDate>>
                    {
                        code = (int)RespCodeEnum.Success,
                        data = tankDates,
                        total = tankDates.Count,
                        //  msg = msg
                    };
                }
            }
            else
            {
                return new Result<List<TankDate>>
                {
                    code = (int)RespCodeEnum.Failed,
                    msg = "必填字段信息未填写！"
                };
            }
        }


        /// <summary>
        /// 调用存储过程获取储罐数据
        /// </summary>
        /// <returns></returns>
        private List<TankView> GetTankViews(bool bl = true)
        {
            var date = DateTime.Now.Date;
            var sql = $@"EXEC JinHai.AIS20201103082022.dbo.easy_210708_ygdtb17 '{date}','{date}' ";
            //SELECT TOP 1000 * FROM [dbo].[SheetDCS] WHERE [fghmc] NOT LIKE '%管%' AND [fghmc] <> N'NULL' AND [fjshs] > N'0'
            //var sql = $@"SELECT * FROM [dbo].[SheetDCS] ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            var list = new List<TankView>();
            foreach (DataRow item in dt.Rows)
            {
                var fjshs = Convert.ToDecimal(item["fjshs"].ToString());
                var fypmc = item["fypmc"].ToString();
                var fghmc = item["fghmc"].ToString();
                if (!fypmc.Contains("计") && !fghmc.Contains("管"))
                {
                    list.Add(new TankView
                    {
                        fghmc = item["fghmc"].ToString(),
                        fjshs = Convert.ToDecimal(item["fjshs"].ToString()),
                        fyoug = Convert.ToDecimal(item["fyoug"].ToString()),
                        fyouw = Convert.ToDecimal(item["fyouw"].ToString()),
                        fypmc = item["fypmc"].ToString(),
                        fzrkc = Convert.ToDecimal(item["fzrkc"].ToString())
                    });
                }
                //if (fjshs > 0 && !fypmc.Contains("计") && !fghmc.Contains("管"))
                //{
                //    list.Add(new TankView
                //    {
                //        fghmc = item["fghmc"].ToString(),
                //        fjshs = Convert.ToDecimal(item["fjshs"].ToString()),
                //        fyoug = Convert.ToDecimal(item["fyoug"].ToString()),
                //        fyouw = Convert.ToDecimal(item["fyouw"].ToString()),
                //        fypmc = item["fypmc"].ToString(),
                //        fzrkc = Convert.ToDecimal(item["fzrkc"].ToString())
                //    });
                //}
            }
            return bl ? list.Where(p => p.fjshs > 0).ToList() : list;
        }
        /// <summary>
        /// 获取油罐数据---停用
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet]
        private Result<List<OilTankM>> GetOilTankLists()
        {
            var a = GetOilTankMs();
            var b = GetDOilTankMs();
            var list = new List<OilTankM>();
            foreach (var item in a)
            {
                var c = new OilTankM()
                {
                    DcsName = item.DcsName,
                    DcsTotalData = Convert.ToDecimal(item.DcsTotalData).ToString("#0"),
                    DcsData = "0.000",
                    DcsDataHeat = "0.00"
                };
                var temp = b.Where(p => p.DcsName == item.DcsName).FirstOrDefault();
                if (temp != null)
                {
                    c.DcsDataHeat = Convert.ToDecimal(temp.DcsDataHeat).ToString("#0.00");
                    c.DcsData = Convert.ToDecimal(temp.DcsData).ToString("#0.000");
                }
                list.Add(c);
            }
            return new Result<List<OilTankM>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"

            };
        }
        /// <summary>
        /// 所有 油罐 
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private List<OilTankZ> GetOilTankMs()
        {
            var sql = $@"SELECT FMC as DcsName,FHeat as DcsDataHeat,FTotalLevel as DcsTotalData FROM PointValues WHERE  FType=1";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            var dsc = new List<OilTankZ>();
            foreach (DataRow item in dt.Rows)
            {
                dsc.Add(new OilTankZ()
                {
                    DcsName = item["DcsName"].ToString(),
                    DcsDataHeat = item["DcsDataHeat"].ToString(),
                    DcsTotalData = item["DcsTotalData"].ToString(),
                });
            }
            return dsc;
        }
        /// <summary>
        /// 油罐 单据值
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private List<OilTankD> GetDOilTankMs()
        {
            var list = new List<OilTankD>();
            var time = DateTime.Now.Date;
            var time1 = time.AddDays(-1);
            var time2 = time.AddDays(-1).AddHours(8);
            var time3 = time.AddHours(8);
            //镇江 油罐
            var sqlzj = $@"SELECT b.FBillNum AS DcsName,a.FDecimal1 AS DcsData,a.FDecimal6 AS DcsDataHeat  FROM t_BOS200000071Entry2 AS a  INNER JOIN  (SELECT * FROM DcsBillStatus Where FType = 1 AND FBillNum != '污水点值' AND FBillCreatTime > '{time1}')  AS  b  ON a.FBase2 = B.FBillNo Where a.FIndex = 13 AND a.FTime > '{time2}' AND a.FTime < '{time3}'";
            var dsczj = DbSession.Default.FromSql(sqlzj).ToList<OilTankD>();
            list.AddRange(dsczj);
            //南京 油罐
            var sqlnj = $@"SELECT b.FBillNum AS DcsName,a.FDecimal1 AS DcsData,a.FDecimal6 AS DcsDataHeat  FROM t_BOS200000070Entry2 AS a  INNER JOIN  (SELECT * FROM DcsBillStatus Where FType = 1 AND FBillNum != '污水点值' AND FBillCreatTime > '{time1}')  AS  b  ON a.FBase2 = B.FBillNo Where a.FIndex = 13 AND a.FTime > '{time2}' AND a.FTime < '{time3}'";
            var dscnj = DbSession.Default.FromSql(sqlnj).ToList<OilTankD>();
            list.AddRange(dscnj);
            return list;
        }
        #endregion

        #region 获取污水数据

        /// <summary>
        /// 污水----------停用
        /// </summary>
        [AllowAnonymous]
        [HttpGet]
        private Result<List<DcsDataM>> GetSewageLists()
        {
            var list = new List<DcsDataM>();
            //var a = GetSewageMs(new SewageD());
            //foreach (var item in a)
            //{
            //    list.Add(new DcsDataM
            //    {
            //        DcsName = item.DcsName,
            //        DcsData = Convert.ToDecimal(item.DcsSewage).ToString("#0.000")
            //    });
            //    //if (item.DcsName == "污水外送")
            //    //{
            //    //    list.Add(new DcsDataM
            //    //    {
            //    //        DcsName = item.DcsName,
            //    //        DcsData = item.DcsFlow
            //    //    });
            //    //}
            //}
            return new Result<List<DcsDataM>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"

            };
        }

        /// <summary>
        /// 所有 污水
        /// </summary>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<SewageD>> GetSewageMs(SewageD sewageD)
        {
            var sql = $@"SELECT FMC as DcsName,FSewage as DcsSewage,FFlow as DcsFlow  FROM PointValues WHERE  FType=2";
            if (!sewageD.IsNullOrEmpty())
            {
                if (!sewageD.DcsName.IsNullOrEmpty())
                {
                    sql += $@" AND FMC LIKE '%{sewageD.DcsName}%'";
                }
                if (!sewageD.DcsSewage.IsNullOrEmpty())
                {
                    sql += $@" AND FSewage='{sewageD.DcsSewage}'";
                }
                if (!sewageD.DcsFlow.IsNullOrEmpty())
                {
                    sql += $@" AND FFlow='{sewageD.DcsFlow}'";
                }
            }
            var dt = DbHelperSQL.Query(sql).Tables[0];
            var list = new List<SewageD>();
            foreach (DataRow item in dt.Rows)
            {
                var DcsFlow = Convert.ToDecimal(item["DcsFlow"].ToString());
                list.Add(new SewageD()
                {
                    DcsName = item["DcsName"].ToString(),
                    DcsFlow = DcsFlow == 0 ? "" : DcsFlow.ToString(),
                    DcsSewage = item["DcsSewage"].ToString(),
                });
            }
            return new Result<List<SewageD>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"

            };
        }
        #endregion

        #region 获取车辆状态  全部  已入场 已过皮重 已过总重 已出场
        /// <summary>
        /// 所有
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<CarStatus>> GetCarAllLists()
        {
            var list = GetRuChang();
            list.AddRange(GetPiZhong(1));
            list.AddRange(GetPiZhong(2));
            list.AddRange(GetChuChang());
            var com=list.DistinctBy(p => p.FCarName).ToList();
            return new Result<List<CarStatus>>
            {
                code = (int)RespCodeEnum.Success,
                data = com,
                total = com.Count,
                msg = "成功"
            };
        }
        /// <summary>
        /// 已入场
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<CarStatus>> GetCarInLists()
        {
            var list = GetRuChang();
            return new Result<List<CarStatus>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"
            };
        }

        /// <summary>
        /// 已过皮重
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<CarStatus>> GetCarTareLists()
        {
            var list = GetPiZhong(1);
            return new Result<List<CarStatus>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"
            };
        }

        /// <summary>
        /// 已过总重
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<CarStatus>> GetCarTotalLists()
        {
            var list = GetPiZhong(2);
            return new Result<List<CarStatus>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"
            };
        }
        /// <summary>
        /// 已出场
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<CarStatus>> GetCarOutLists()
        {
            var list = GetChuChang();
            return new Result<List<CarStatus>>
            {
                code = (int)RespCodeEnum.Success,
                data = list,
                total = list.Count,
                msg = "成功"
            };
        }

        private List<CarStatus> GetRuChang()
        {
            var list = new List<CarStatus>();
            var now = DateTime.Now.Date;
            var sql = $@" SELECT 
                                             a.inRecordId,
                                             a.inTime as FInTime,
                                             a.plateNumber as FCarName,
                                             b.Cargo_TiDanHao as FBillNo, 
                                        b.Cargo_YouPinMing as FWuliaoName, 
                                        b.Cargo_CheHao as FCarName, 
                                        b.isState as FStatus, 
                                       b. Cargo_ChuKuRiQi as FTotalTime , 
                                        b.Cargo_ZhuangHuoRiQi as FTareTime, 
                                        b.Cargo_ZongZhong as FTotalWeight, 
                                       b. Cargo_KongCheZhong as FTareWeight, 
                                        b.Cargo_JingZhong as FNetWeight ,
                                        b.Cargo_ShouHuoDanWei as FTenantName
                                     FROM DcsDB.dbo.carinM  as a
                                    left join 佛山惠福科创有限公司.dbo.Cargo as b on a.plateNumber=b.Cargo_CheHao
                                        WHERE inTime>='{now}' ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            foreach (DataRow item in dt.Rows)
            {
                list.Add(new CarStatus()
                {
                    FBillNo = item["FBillNo"].ToString(),
                    FCarName = item["FCarName"].ToString(),
                    FStatus = "已入场",
                    FWuliaoName = item["FWuliaoName"].ToString(),
                    FInTime = item["FInTime"].ToString(),
                    FNetWeight = item["FNetWeight"].ToString(),
                    FOutTime = item["FOutTime"].ToString(),
                    FTareWeight = item["FTareWeight"].ToString(),
                    FTotalWeight = item["FTotalWeight"].ToString(),
                    FTenantName = item["FTenantName"].ToString(),
                    FTareTime = item["FTareTime"].ToString(),
                    FTotalTime = item["FTotalTime"].ToString(),
                    //FStatusTime = s == 1 ? (Convert.ToDateTime(item["FCreatTime"].ToString())).ToString("yyyy-MM-dd HH:mm") : (Convert.ToDateTime(item["FOutTime"].ToString())).ToString("yyyy-MM-dd HH:mm")
                });
            }
            return list;
        }

        private List<CarStatus> GetPiZhong(int isState)
        {
            var list = new List<CarStatus>();
            var now = DateTime.Now.Date;
            var sql = $@" SELECT 
                                        Cargo_TiDanHao as FBillNo, 
                                        Cargo_YouPinMing as FWuliaoName, 
                                        Cargo_CheHao as FCarName, 
                                        isState as FStatus, 
                                        Cargo_ChuKuRiQi as FTotalTime , 
                                        Cargo_ZhuangHuoRiQi as FTareTime, 
                                        Cargo_ZongZhong as FTotalWeight, 
                                        Cargo_KongCheZhong as FTareWeight, 
                                        Cargo_JingZhong as FNetWeight ,
                                        Cargo_ShouHuoDanWei as FTenantName
                                   FROM 佛山惠福科创有限公司.dbo.Cargo  
                                   WHERE isState={isState} AND  Cargo_ZhuangHuoRiQi >='{now}'";

            var dt = DbHelperSQL.Query(sql).Tables[0];
            foreach (DataRow item in dt.Rows)
            {
                 //var s = int.Parse(item["FStatus"].ToString());
                list.Add(new CarStatus()
                {
                    FBillNo = item["FBillNo"].ToString(),
                    FCarName = item["FCarName"].ToString(),
                    FStatus = "已过皮重",
                    FWuliaoName = item["FWuliaoName"].ToString(),
                    FTareTime = item["FTareTime"].ToString(),
                    FTotalTime = item["FTotalTime"].ToString(),
                    //FInTime = item["FInTime"].ToString(),
                    FNetWeight = item["FNetWeight"].ToString(),
                    //FOutTime = item["FOutTime"].ToString(),
                    FTareWeight = item["FTareWeight"].ToString(),
                    FTotalWeight = item["FTotalWeight"].ToString(),
                    FTenantName = item["FTenantName"].ToString(),
                    //FStatusTime = s == 1 ? (Convert.ToDateTime(item["FCreatTime"].ToString())).ToString("yyyy-MM-dd HH:mm") : (Convert.ToDateTime(item["FOutTime"].ToString())).ToString("yyyy-MM-dd HH:mm")
                });
            }
            return list;
        }

        private List<CarStatus> GetChuChang()
        {
            var list = new List<CarStatus>();
            var now = DateTime.Now.Date;
            var sql = $@" SELECT 
                                             a.outRecordId,
                                             a.inRecordId,
                                             a.outTime as FOutTime,
                                             a.plateNumber as FCarName,
                                             b.Cargo_TiDanHao as FBillNo, 
                                        b.Cargo_YouPinMing as FWuliaoName, 
                                        b.Cargo_CheHao as FCarName, 
                                        b.isState as FStatus, 
                                       b. Cargo_ChuKuRiQi as FTotalTime , 
                                        b.Cargo_ZhuangHuoRiQi as FTareTime, 
                                        b.Cargo_ZongZhong as FTotalWeight, 
                                       b. Cargo_KongCheZhong as FTareWeight, 
                                        b.Cargo_JingZhong as FNetWeight ,
                                        b.Cargo_ShouHuoDanWei as FTenantName
                                     FROM DcsDB.dbo.caroutM  as a
                                    left join 佛山惠福科创有限公司.dbo.Cargo as b on a.plateNumber=b.Cargo_CheHao
                                        WHERE outTime>='{now}' ";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            foreach (DataRow item in dt.Rows)
            {
                list.Add(new CarStatus()
                {
                    FBillNo = item["FBillNo"].ToString(),
                    FCarName = item["FCarName"].ToString(),
                    FStatus = "已入场",
                    FWuliaoName = item["FWuliaoName"].ToString(),
                    FInTime = item["FInTime"].ToString(),
                    FNetWeight = item["FNetWeight"].ToString(),
                    FOutTime = item["FOutTime"].ToString(),
                    FTareWeight = item["FTareWeight"].ToString(),
                    FTotalWeight = item["FTotalWeight"].ToString(),
                    FTenantName = item["FTenantName"].ToString(),
                    FTareTime = item["FTareTime"].ToString(),
                    FTotalTime = item["FTotalTime"].ToString(),
                    //FStatusTime = s == 1 ? (Convert.ToDateTime(item["FCreatTime"].ToString())).ToString("yyyy-MM-dd HH:mm") : (Convert.ToDateTime(item["FOutTime"].ToString())).ToString("yyyy-MM-dd HH:mm")
                });
            }
            return list;
        }

        #endregion

        #region 获取能源信息

        /// <summary>
        /// 获取能源信息
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<SyncEnergyData>> GetSyncEnergyLists(SyncEnergyData syncEnergyData)
        {
            var sql = $@"SELECT TOP 1 a.FID,a.FDate FROM JinHai.AIS20201103082022.dbo.t_BOS200000072 AS a   WHERE  a.FDate='{DateTime.Now.Date}'  ORDER BY a.FDate DESC";
            if (!syncEnergyData.IsNullOrEmpty())
            {
                if (!syncEnergyData.FDate.IsNullOrEmpty())
                {
                    sql = $@"SELECT TOP 1 a.FID,a.FDate FROM JinHai.AIS20201103082022.dbo.t_BOS200000072 AS a WHERE  a.FDate='{syncEnergyData.FDate}'  ORDER BY a.FDate DESC";
                }
            }
            var dt = DbHelperSQL.Query(sql).Tables[0];
            var fids = string.Empty;
            if (dt.Rows.Count > 0)
            {
                fids = dt.Rows[0]["FID"].ToString();
            }
            //for (int i = 0; i < dt.Rows.Count; i++)
            //{
            //    if (i == 0)
            //    {
            //        fids += dt.Rows[i]["FID"].ToString() + ",";
            //    }
            //    else
            //    {
            //        fids += dt.Rows[i]["FID"].ToString();
            //    }
            //}
            if (fids == string.Empty)
            {
                return new Result<List<SyncEnergyData>>
                {
                    code = (int)RespCodeEnum.Failed,
                    msg = "为找到能源数据表中的数据"
                };
            }
            else
            {
                var sqldate = $@"SELECT a.FID,a.FDate,b.FBase1,c.FName,c.F_102,c.F_103,b.FDecimal FROM JinHai.AIS20201103082022.dbo.t_BOS200000072 AS a INNER JOIN  JinHai.AIS20201103082022.dbo.t_BOS200000072Entry2 AS b ON a.FID = b.FID INNER JOIN JinHai.AIS20201103082022.dbo.t_Item_3042 AS c ON b.FBase1 = c.FItemID WHERE a.FID IN({fids})";
                if (!syncEnergyData.IsNullOrEmpty())
                {
                    if (!syncEnergyData.FDecimal.IsNullOrEmpty())
                    {
                        sqldate += $@" AND b.FDecimal='{syncEnergyData.FDecimal}'";
                    }
                    if (!syncEnergyData.FName.IsNullOrEmpty())
                    {
                        sqldate += $@" AND c.FName LIKE '%{syncEnergyData.FName}%'";
                    }
                    if (!syncEnergyData.F_102.IsNullOrEmpty())
                    {
                        sqldate += $@" AND c.F_102='{syncEnergyData.F_102}'";
                    }
                    if (!syncEnergyData.F_103.IsNullOrEmpty())
                    {
                        sqldate += $@" AND c.F_103='{syncEnergyData.F_103}'";
                    }
                }
                var dtdate = DbHelperSQL.Query(sqldate).Tables[0];
                var list = new List<SyncEnergyData>();
                foreach (DataRow item in dtdate.Rows)
                {
                    list.Add(new SyncEnergyData()
                    {
                        FBase1 = item["FBase1"].ToString(),
                        FDate = item["FDate"].ToString(),
                        FDecimal = Math.Round(Convert.ToDecimal(item["FDecimal"].ToString()), 2),
                        FName = item["FName"].ToString(),
                        FID = Convert.ToInt32(item["FID"].ToString()),
                        F_102 = item["F_102"].ToString(),
                        F_103 = item["F_103"].ToString()
                    });
                }
                return new Result<List<SyncEnergyData>>
                {
                    code = (int)RespCodeEnum.Success,
                    data = list,
                    total = list.Count,
                    msg = "成功"
                };

            }
        }

        #endregion

        #region 云之家推送数据
        /// <summary>
        /// 云之家推送数据，非大屏接口
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Yzjm GetYzjDate(object date)
        {
            DBMain.LogsDebug(date);
            return new Yzjm();
        }
        public class Yzjm
        {
            public bool success { get; set; } = true;
            public int errorCode { get; set; } = 0;
            public object error { get; set; } = null;
        }
        #endregion

        #region 三级检查表
        /// <summary>
        /// 三级检查表
        /// </summary>
        [AllowAnonymous]
        [HttpPost]
        public Result<List<InspectDateM>> GetInspectDate()
        {
            var date = DateTime.Now.Date;
            var enddate = date.AddDays(-90);
            var sql = $@"SELECT a.FID,a.FDate,a.FText,b.FText1,b.FText2,b.FText3,b.FText4 FROM JinHai.AIS20201103082022.dbo.t_BOS200000093  AS a INNER JOIN JinHai.AIS20201103082022.dbo.t_BOS200000093Entry2 AS b ON b.FID = a.FID WHERE a.FDate<='{date}' AND a.FDate>='{enddate}'  ORDER BY a.FDate DESC";
            var dt = DbHelperSQL.Query(sql).Tables[0];
            var inspect = new List<InspectDateM>();
            foreach (DataRow item in dt.Rows)
            {
                inspect.Add(new InspectDateM()
                {
                    FID = int.Parse(item["FID"].ToString()),
                    FDate = (DateTime.Parse(item["FDate"].ToString())).ToString("yyyy-MM-dd"),
                    FText = item["FText"].ToString(),
                    FText1 = item["FText1"].ToString(),
                    FText2 = item["FText2"].ToString(),
                    FText3 = item["FText3"].ToString(),
                    FText4 = item["FText4"].ToString()
                });
            }
            return new Result<List<InspectDateM>>
            {
                code = (int)RespCodeEnum.Success,
                data = inspect,
                total = inspect.Count,
                msg = "成功"
            };
        }
        #endregion

        /// <summary>
        /// 下拉框选择
        /// </summary>
        /// <param name="ftype">1 能源 2 污水</param>
        /// <returns></returns>
        [AllowAnonymous]
        [HttpGet]
        public Result<List<NameData>> GetSewageEnergy(int ftype)
        {
            if (ftype == 1)
            {
                var sqldate = $@"SELECT c.FName FROM JinHai.AIS20201103082022.dbo.t_BOS200000072 AS a INNER JOIN  JinHai.AIS20201103082022.dbo.t_BOS200000072Entry2 AS b ON a.FID = b.FID INNER JOIN JinHai.AIS20201103082022.dbo.t_Item_3042 AS c ON b.FBase1 = c.FItemID WHERE a.FDate='{DateTime.Now.Date}' AND c.FName NOT IN (SELECT Fname FROM SewageEnergy WHERE  Ftype=1);";
                var dtdate = DbHelperSQL.Query(sqldate).Tables[0];
                var sql2 = $@"SELECT Fname FROM SewageEnergy WHERE Ftype = 1";
                var dt2 = DbHelperSQL.Query(sql2).Tables[0];
                var list = new List<string>();
                var listAll = new List<NameData>();
                int i = 0;
                if (dtdate.Rows.Count > 0)
                {
                    foreach (DataRow item in dtdate.Rows)
                    {
                        //有不同值
                        var temp = item["FName"].ToString();
                        list.Add(temp);
                        i = i + 1;
                        listAll.Add(new NameData()
                        {
                            Fid = i.ToString(),
                            FName = temp,
                        });
                    }
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt2.Rows)
                        {
                            i = i + 1;
                            listAll.Add(new NameData()
                            {
                                Fid = i.ToString(),
                                FName = item["Fname"].ToString(),
                            });
                            // listAll.Add(item["Fname"].ToString());
                        }
                    }
                }
                else
                {
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt2.Rows)
                        {
                            //listAll.Add(item["Fname"].ToString());
                            i = i + 1;
                            listAll.Add(new NameData()
                            {
                                Fid = i.ToString(),
                                FName = item["Fname"].ToString(),
                            });
                        }
                    }
                }
                if (list.Count > 0)
                {
                    string isql = "";
                    foreach (var item in list)
                    {
                        isql += $@"INSERT INTO SewageEnergy(Fname, Ftype) VALUES('{item}', 1);";
                    }
                    DbHelperSQL.ExecuteSql(isql);
                }
                var com = listAll.DistinctBy(p => p.FName).ToList();
                return new Result<List<NameData>>
                {
                    code = (int)RespCodeEnum.Success,
                    data = com,
                    total = com.Count,
                    msg = "成功！"
                };

            }
            else if (ftype == 2)//污水
            {
                var sql1 = $@"SELECT FMC FROM PointValues WHERE  FType=2  AND FMC NOT IN (SELECT Fname FROM SewageEnergy WHERE  Ftype=2)";
                var dt1 = DbHelperSQL.Query(sql1).Tables[0];
                var sql2 = $@"SELECT Fname FROM SewageEnergy WHERE Ftype = 2";
                var dt2 = DbHelperSQL.Query(sql2).Tables[0];
                var list = new List<string>();
                var listAll = new List<NameData>();
                int i = 0;
                if (dt1.Rows.Count > 0)
                {
                    foreach (DataRow item in dt1.Rows)
                    {
                        //有不同值
                        var temp = item["FMC"].ToString();
                        list.Add(temp);
                        i = i + 1;
                        listAll.Add(new NameData()
                        {
                            Fid = i.ToString(),
                            FName = temp,
                        });
                        // listAll.Add(temp);
                    }
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt2.Rows)
                        {
                            // listAll.Add(item["Fname"].ToString());
                            i = i + 1;
                            listAll.Add(new NameData()
                            {
                                Fid = i.ToString(),
                                FName = item["Fname"].ToString(),
                            });
                        }
                    }
                }
                else
                {
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow item in dt2.Rows)
                        {
                            // listAll.Add(item["Fname"].ToString());
                            i = i + 1;
                            listAll.Add(new NameData()
                            {
                                Fid = i.ToString(),
                                FName = item["Fname"].ToString(),
                            });
                        }
                    }
                }
                if (list.Count > 0)
                {
                    string isql = "";
                    foreach (var item in list)
                    {
                        isql += $@"INSERT INTO SewageEnergy(Fname, Ftype) VALUES('{item}', 2);";
                    }
                    DbHelperSQL.ExecuteSql(isql);
                }
                var com = listAll.DistinctBy(p => p.FName).ToList();
                return new Result<List<NameData>>
                {
                    code = (int)RespCodeEnum.Success,
                    data = com,
                    total = com.Count,
                    msg = "成功！"
                };
            }
            else
            {
                return new Result<List<NameData>>
                {
                    code = (int)RespCodeEnum.Failed,
                    msg = "参数值不对！"
                };
            }
        }
    }
}